﻿Public Class CreateTrigger
    Implements ITrigger

    Public Event CreateTableTrigger(ByVal table As String) Implements IListenDb.ITrigger.CreateTableTrigger
    Public Event DeleteTableTrigger(ByVal table As String) Implements IListenDb.ITrigger.DeleteTableTrigger
    Public Event UpdateTableTrigger(ByVal table As String) Implements IListenDb.ITrigger.UpdateTableTrigger

    Public Function CreateTrigger(ByVal dbConnection As System.Data.Common.DbConnection, ByVal 前缀 As String) As Boolean Implements IListenDb.ITrigger.CreateTrigger

        Dim sqlCreate As String = ""
        Dim sql As String = ""

        Dim dt表 As New DataTable
        Dim dt列 As New DataTable
        Dim dt表列 As New DataTable

        '获取所有表
        sql = "select name,object_id from sys.tables"
        dt表 = CommonConUtil.GetTable(sql)

        '获取所有列
        sql = " select c.object_id,c.name columnname,c.max_length,c.precision,c.scale,t.name typename from sys.columns c "
        sql += " LEFT JOIN sys.types t"
        sql += " on c.system_type_id=t.system_type_id"
        sql += " where t.name<>'sysname'"
        dt列 = CommonConUtil.GetTable(sql)

        Dim dv As DataView = dt列.DefaultView

        For rowTable As Integer = 0 To dt表.Rows.Count - 1

            dv.RowFilter = "object_id=" & dt表.Rows(rowTable)("object_id")
            dt表列 = dv.ToTable

            If Not dt表.Rows(rowTable)("name").ToString.StartsWith(前缀) Then
                '创建
                sqlCreate = CommonTableToTrigger.ConvertToInsertTrigger(dt表列, dt表.Rows(rowTable)("name"), 前缀)
                CommonConUtil.ExecuteSql(sqlCreate)
                RaiseEvent CreateTableTrigger("创建插入触发器 " & 前缀 & "_I_" & CStr(dt表.Rows(rowTable)("name")))

                sqlCreate = CommonTableToTrigger.ConvertToUpdateTrigger(dt表列, dt表.Rows(rowTable)("name"), 前缀)
                CommonConUtil.ExecuteSql(sqlCreate)
                RaiseEvent CreateTableTrigger("创建更新触发器 " & 前缀 & "_U_" & CStr(dt表.Rows(rowTable)("name")))

                sqlCreate = CommonTableToTrigger.ConvertToDeleteTrigger(dt表列, dt表.Rows(rowTable)("name"), 前缀)
                CommonConUtil.ExecuteSql(sqlCreate)
                RaiseEvent CreateTableTrigger("创建删除触发器 " & 前缀 & "_D_" & CStr(dt表.Rows(rowTable)("name")))
            End If
        Next

    End Function

    Public Function DeleteTrigger(ByVal dbConnection As System.Data.Common.DbConnection, ByVal 前缀 As String) As Boolean Implements IListenDb.ITrigger.DeleteTrigger

        Dim sql As String = ""
        Dim dt触发器 As New DataTable
        Dim dt控制表 As New DataTable

        sql = "Select * from " + 前缀 + "_Control "
        dt控制表 = CommonConUtil.GetTable(sql)

        sql = "select name,object_id from sys.triggers"
        dt触发器 = CommonConUtil.GetTable(sql)

        Dim str删除前缀 As String = 前缀.ToLower
        If dt控制表.Rows.Count > 0 Then
            If CStr(dt控制表.Rows(0)("prefix")).ToLower.Trim <> "" Then
                str删除前缀 = CStr(dt控制表.Rows(0)("prefix")).ToLower.Trim
            End If
        End If

        '删除触发器
        For rowTable As Integer = 0 To dt触发器.Rows.Count - 1
            If (CStr(dt触发器.Rows(rowTable)("name")).ToLower.StartsWith(str删除前缀.ToLower)) Then
                CommonConUtil.ExecuteSql(" drop trigger " & CStr(dt触发器.Rows(rowTable)("name")))
                RaiseEvent DeleteTableTrigger("删除触发器 " & CStr(dt触发器.Rows(rowTable)("name")))
            End If
        Next

    End Function

    Public Function UpdateTrigger(ByVal dbConnection As System.Data.Common.DbConnection, ByVal 前缀 As String) As Boolean Implements IListenDb.ITrigger.UpdateTrigger

    End Function

End Class

